import java.io.IOException;
import java.io.PrintWriter;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.net.SocketAddress;
import java.net.SocketException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//All methods that access the DB here.
public class ServerDB {
	private final String dbname = "chat_server";
	private SQLiteDatabase db = null;
	private Statement s = null;
	private int dbcounter = 0;
	private ResultSet rs = null;
	private int affected;

	public ServerDB() {
		db = new SQLiteDatabase();
	}
	
	
	public void createDatabase() throws ClassNotFoundException, SQLException {
		connect();
		s.addBatch("CREATE TABLE IF NOT EXISTS Users ( "+
			"Username varchar(30), "+
			"Password varchar(30) NOT NULL, "+
			"IP varchar(15), "+
			"Port int(4), "+
			//"OnlineStatus varchar(160), "+
			"PRIMARY KEY(Username));" );
		s.addBatch("CREATE TABLE IF NOT EXISTS Friends ( "+
			"User1 varchar(30), "+
			"User2 varchar(30), "+
			"PRIMARY KEY(User1, User2), "+
			"FOREIGN KEY (User1) REFERENCES User(Username) ON DELETE CASCADE ON UPDATE CASCADE, "+
			"FOREIGN KEY (User2) REFERENCES User(Username) ON DELETE CASCADE ON UPDATE CASCADE);");
		s.executeBatch();
		disconnect();
	}

	public boolean auth(String user, String pass) throws SQLException, ClassNotFoundException {
		connect();
		query("SELECT * FROM Users WHERE Username = '"+user+"' AND Password = '"+pass+"';");
		boolean res = rs.next();
		disconnect();
		return res;
	}

	//Logs the user's IP in the database
	public boolean setLogin(String user, int Port, SocketAddress addr) throws ClassNotFoundException, SQLException {
		InetSocketAddress iaddr = (InetSocketAddress) addr;

		connect();
		String IP = iaddr.getHostName();
		System.out.println("UPDATE Users SET IP = '"+IP+"', Port = "+Port+" WHERE Username = '"+user+"';");
		update("UPDATE Users SET IP = '"+IP+"', Port = "+Port+" WHERE Username = '"+user+"';");
		disconnect();
		System.out.println(affected);
		return affected > 0;
	}
	//Logs the user's IP in the database
	public boolean register(String user, String password, SocketAddress addr) throws ClassNotFoundException, SQLException {
		InetSocketAddress iaddr = (InetSocketAddress) addr;

		connect();
		String IP = iaddr.getHostName();
		int Port = iaddr.getPort();
		System.out.println("INSERT INTO Users (Username, Password, IP, Port) " +
				"VALUES ('"+user+"', '"+password+"', '"+IP+"', "+Port+");");
		update("INSERT INTO Users (Username, Password, IP, Port) " +
				"VALUES ('"+user+"', '"+password+"', '"+IP+"', "+Port+");");
		disconnect();
		return affected > 0;
	}

	public void sendFriendsList(String user, PrintWriter os) throws IOException, ClassNotFoundException, SQLException {
		connect();
		query("SELECT u.Username, u.IP, u.Port FROM Friends AS f " +
				"LEFT JOIN Users AS u ON f.User2 = u.Username " +
				"WHERE f.User1 = '"+user+"';");

		os.println("Userlist");

		//Write out all rows
		while(rs.next()) {
			os.println(rs.getString(1));
			os.println(rs.getString(2));
			os.println(rs.getString(3));
			System.out.println("|"+rs.getString(1)+"|"+rs.getString(2)+"|"+rs.getString(3)+"|");
		}
		
		//os.println("{}");

		//Close
		disconnect();
		os.close();
	}
	
	public void sendOnlineStatus(String msg, String usr, PrintWriter os) throws ClassNotFoundException, SQLException {
		connect();
		query("SELECT IP, Port FROM Users WHERE Username = '"+usr+"';");
		
		if(!rs.next()) return;
		os.println(msg);
		os.println(rs.getString(1));
		os.println(rs.getString(2));
		System.out.println("|"+msg+"|"+rs.getString(1)+"|"+rs.getString(2)+"|");
		disconnect();//*/
	}
	
	public void sendUpdates(String msg, String aboutUsr, Socket sckt) throws ClassNotFoundException, SQLException {
		System.out.println("Enter Update ("+msg+") for "+aboutUsr);
		connect();
		query("SELECT IP, Port FROM Users WHERE Username = '"+aboutUsr+"';");
		if(!rs.next()) return;
		String UsrIP = rs.getString(1);
		String UsrPort = rs.getString(2);		
		
		query("SELECT u.IP, u.Port, u.Username FROM Users AS u LEFT JOIN Friends AS f " +
				" ON u.Username = f.User2 WHERE User1 = '"+aboutUsr+"';");
		
		String addr = null;
		PrintWriter os = null;
		int port = 0;
		InetSocketAddress remote = null;
		while(rs.next()) {
			addr = rs.getString(1);
			port = rs.getInt(2);
			String usr = rs.getString(3);
			System.out.print("Connect to ("+usr+") "+addr+":");
			System.out.println(port);
			if(addr != null && !addr.equals("")) try {
				remote = new InetSocketAddress(addr, port);
				sckt = new Socket(addr, port);
				os = new PrintWriter(sckt.getOutputStream());
				os.println(msg);
				os.println(aboutUsr);
				os.println(UsrIP);
				os.println(UsrPort);
				os.close();
			} catch (SocketException e) {
				System.out.println(e.getMessage());
				System.out.println("Forcing logout");
				
				//if(logout(usr))
				//	sendUpdates("logout", usr, sckt);
			} catch (IOException e) {
				System.out.println(remote.toString());
				e.printStackTrace();
			}
		}
		disconnect();//*/
	}
	
	public void sendUpdate(String msg, String toUsr, String aboutUsr) throws ClassNotFoundException, SQLException {
		connect();
		query("SELECT IP, Port FROM Users WHERE Username = '"+aboutUsr+"';");
		if(!rs.next()) return;
		String UsrIP = rs.getString(1);
		String UsrPort = rs.getString(2);
		
		query("SELECT IP, Port FROM Users WHERE Username = '"+toUsr+"';");
		if(!rs.next()) return;
		String addr = rs.getString(1);
		int port = Integer.getInteger(rs.getString(2));	
		
		try {
			Socket sckt = new Socket(addr, port);
			PrintWriter os = new PrintWriter(sckt.getOutputStream());
			os.println(msg);
			os.println(aboutUsr);
			os.println(UsrIP);
			os.println(UsrPort);
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		disconnect();
	}
	
	public boolean addfriend(String user1, String user2, PrintWriter os) throws ClassNotFoundException, SQLException {
		connect();
		update("INSERT INTO Friends (User1, User2) VALUES ('"+user1+"', '"+user2+"');");
		update("INSERT INTO Friends (User2, User1) VALUES ('"+user1+"', '"+user2+"');");
		disconnect();
		if(affected > 0) {
			os.println("true");
			sendOnlineStatus(user2, user2, os);
		} else {
			for(int i=0; i<4; i++)
				os.println("false");
		}
		return affected > 0;
	}

	public boolean removefriend(String user1, String user2) throws ClassNotFoundException, SQLException {
		connect();
		update("DELETE FROM Friends WHERE friends.User1 = '"+user1+"' AND friends.User2 = '"+user2+"';");
		update("DELETE FROM Friends WHERE friends.User1 = '"+user2+"' AND friends.User2 = '"+user1+"';");
		disconnect();
		return affected > 0;
	}	

	public boolean logout(String user) throws SQLException, ClassNotFoundException {
		connect();
		update("UPDATE Users SET IP = NULL, Port = 0 WHERE Username='"+user+"';");
		disconnect();
		
		if(affected==0) {
			System.out.println("Update query error");
			System.out.println("UPDATE Users SET IP = NULL, Port = 0 WHERE Username='"+user+"';");
			return false;
		}
		return true; 
	}

	public boolean userExists(String user) throws ClassNotFoundException, SQLException {
		connect();
		query("SELECT * FROM Users WHERE Username = '"+user+"';");
		boolean res = rs.next();
		disconnect();
		return res;
	}

	public boolean changePwd(String user, String newPwd) throws ClassNotFoundException, SQLException {
		connect();
		update("UPDATE Users SET Password = '"+newPwd+"' WHERE Username = '"+user+"';");
		disconnect();
		return affected > 0;
	}

	//If we have to do locking, here's where we'd free it
	private void disconnect() {
		if(--dbcounter<=0) {
			db.closeDatabase();
			s = null;
			dbcounter = 0;
		}
	}

	//Use this so we fix dbname
	//Also could handle some sort of locking if need be
	//Right now sharing the connection
	private void connect() throws ClassNotFoundException, SQLException {
		++dbcounter;
		if(s==null) {
			s = db.connect(dbname);
		}
	}

	private void query(String str) throws SQLException {
		rs = s.executeQuery(str);
	}

	private void update(String str) throws SQLException {
		affected = s.executeUpdate(str);
	}
}
